﻿using Org.BouncyCastle.Tls;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml.Serialization;

namespace ClinicSystem.configure
{
	public static class Load_OutFile
	{
		static SQLiteConnection Connection;
		/// <summary>
		/// 连接到数据库
		/// </summary>
		public static void ConnectionDB(string DBName)
		{
			Connection = new SQLiteConnection("DATA SOURCE=" + DBName + ".db;Version=3");
			Connection.Open();
		}
		/// <summary>
		/// 创建表,
		/// </summary>
		/// <param name="tablename">表名称</param>
		/// <param name="name_type">列名_类型，name VARCHAR(20)</param>
		public static void CreatTable(string tablename, List<string> name_type)
		{
			string head = "";

			foreach (string nametype in name_type)
			{
				head += nametype + ",";
			}
			head = head.Remove(head.Length - 1, 1);
			string sqlcmd = "CREATE TABLE IF NOT EXISTS " + tablename + "(" + head + ")";// "(name VARCHAR(20),gender VARCHAR(20),age INT,MedicalID VARCHER(20))";
			SQLiteCommand cmd = new SQLiteCommand(sqlcmd, Connection);
			cmd.ExecuteNonQuery();
		}

		public static void InsertData(string tablename, List<string> name, List<string> value)
		{
			if (name.Count != value.Count)
			{
				MessageBox.Show("输入数据错误");
				return;
			}
			
			string colname = "";
			string colvalue = "";
			foreach (string names in name)
			{
				colname += names + ",";
			}
			colname = colname.Remove(colname.Length - 1, 1);//移除最后一个逗号
			foreach(string val in value)
			{
				colvalue += val + ",";
			}
			colvalue = colvalue.Remove(colvalue.Length - 1, 1);
			string sqlcmd = "INSERT INTO " + tablename + " (" + colname + ") " + "VALUES (" + colvalue + ")";
			SQLiteCommand cmd = new SQLiteCommand(sqlcmd, Connection);
			cmd.ExecuteNonQuery();
		}

		public static List<string> QueryData(string tablename,string colname,string condition)
		{
			string sqlcmd = "SELECT " + colname + " FROM " + tablename+" WHERE "+condition;
			SQLiteCommand command = new SQLiteCommand(sqlcmd, Connection);
			SQLiteDataReader reader = command.ExecuteReader();
			List<string> result = new List<string>();
			while (reader.Read())
			{
                for (int i = 0; i < reader.FieldCount; i++)
                {
					result.Add(reader[i].ToString());
                    //Console.Write($"{reader[i]},");
                }
                
			}
			return result;
			//var t1 = (n: 42, u: "struc");
		}

		public static List<string> CmdQuery(string cmd)
		{
			SQLiteCommand command = new SQLiteCommand(cmd, Connection);
			SQLiteDataReader reader = command.ExecuteReader();
            List<string> result = new List<string>();
            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    result.Add(reader[i].ToString());
                    //Console.Write($"{reader[i]},");
                }

            }
            return result;
        }
		/// <summary>
		/// 
		/// </summary>
		/// <param name="tableName">表名</param>
		/// <param name="varName">表中的列名</param>
		/// <param name="realName">列中要删除的这一行名</param>
		public static void CmdDelete(string cmd)
		{
			try
			{
				SQLiteCommand command = new SQLiteCommand(cmd, Connection);

				command.ExecuteNonQuery();
			}catch(Exception E)
			{
				MessageBox.Show(E.Message);
			}
		}
	}
}
